Unlike other database APIs you might have used in the past, ADO.NET does not provide a single set of types that communicate with multiple database management systems (DBMSs). Rather, ADO.NET supports multiple data providers, each of which is optimized to interact with a specific DBMS. The first benefit of this approach is that you can program a specific data provider to access any unique features of a particular DBMS. The second benefit is that a specific data provider can connect directly to the underlying engine of the DBMS in question without an intermediate mapping layer standing between the tiers.
Simply put, a data provider is a set of types defined in a given namespace that understand how to communicate with a specific type of data source. Regardless of which data provider you use, each defines a set of class types that provide core functionality. Table 21-1 documents some of the core common types, their base class (all defined in the System.Data.Common namespace), and the key interfaces (each is defined in the System.Data namespace) they implement.
Table 21-1. The Core Objects of an ADO.NET Data Provider
| Type of Object | Base Class | Relevant Interfaces | Meaning in Life |
|---|---|---|---|
| Connection | DbConnection | IDbConnection | Provides the ability to connect to and disconnect from the data store. Connection objects also provide access to a related transaction object. |
| Command | DbCommand | IDbCommand | Represents a SQL query or a stored procedure. Command objects also provide access to the provider’s data reader object. |
| DataReader | DbDataReader | IDataReader, IDataRecord | Provides forward-only, read-only access to data using a server-side cursor. |
| DataAdapter | DbDataAdapter | IDataAdapter, IDbDataAdapter | Transfers DataSets between the caller and the data store. Data adapters contain a connection and a set of four internal command objects used to select, insert, update, and delete information from the data store. |
| Parameter | DbParameter | IDataParameter, IDbDataParameter | Represents a named parameter within a parameterized query. |
| Transaction | DbTransaction | IDbTransaction | Encapsulates a database transaction. |
Although the specific names of these core classes will differ among data providers (e.g., SqlConnection vs. OracleConnection vs. OdbcConnection vs. MySqlConnection), each class derives from the same base class (DbConnection, in the case of connection objects) that implements identical interfaces (e.g., IDbConnection). Given this, you would be correct to assume that once you learn how to work with one data provider, the remaining providers prove quite straightforward.
Note When you refer to a connection object under ADO.NET, you’re actually referring to a specific DbConnection-derived type; there is no class literally named Connection. The same idea holds true for a command object, data adapter object, and so forth. As a naming convention, the objects in a specific data provider are prefixed with the name of the related DBMS (e.g., SqlConnection, OracleConnection, and SqlDataReader).
Figure 21-2 shows the big picture behind ADO.NET data providers. Note how the diagram illustrates that the Client Assembly can literally be any type of .NET application: console program, Windows Forms application, WPF application, ASP.NET web page, WCF service, a .NET code library, and so on.
Figure 21-2 ADO.NET data providers provide access to a given DBMS
A data provider will supply you with other types beyond the objects shown in Figure 21-2; however, these core objects define a common baseline across all data providers.
Microsoft’s .NET distribution ships with numerous data providers, including a provider for Oracle, SQL Server, and OLE DB/ODBC-style connectivity. Table 21-2 documents the namespace and containing assembly for each Microsoft ADO.NET data provider.
Table 21-2. Microsoft ADO.NET Data Providers
|
Data Provider |
Namespace |
Assembly |
|---|---|---|
|
OLE DB |
System.Data.OleDb |
System.Data.dll |
|
Microsoft SQL Server |
System.Data.SqlClient |
System.Data.dll |
|
Microsoft SQL Server Mobile |
System.Data.SqlServerCe |
System.Data.SqlServerCe.dll |
|
ODBC |
System.Data.Odbc |
System.Data.dll |
Note There is no specific data provider that maps directly to the Jet engine (and therefore Microsoft Access). If you wish to interact with an Access data file, you can do so using the OLE DB or ODBC data provider.
The OLE DB data provider, which is composed of the types defined in the System.Data.OleDb namespace, allows you to access data located in any data store that supports the classic COM-based OLE DB protocol. You can use this provider to communicate with any OLE DB–compliant database simply by tweaking the Provider segment of your connection string.
However, the OLE DB provider interacts with various COM objects behind the scenes, which can affect the performance of your application. By and large, the OLE DB data provider is only useful if you interact with a DBMS that does not define a specific .NET data provider. However, given the fact that these days any DBMS worth its salt should have a custom ADO.NET data provider for download, you should consider System.Data.OleDb a legacy namespace that has little use in the .NET 4.0 world (this is even more the case with the advent of the data provider factory model introduced under .NET 2.0, which you will learn about shortly).
Note There is one case in which using the types of System.Data.OleDb is necessary: when you need to communicate with Microsoft SQL Server version 6.5 or earlier. The System.Data.SqlClient namespace can only communicate with Microsoft SQL Server version 7.0 or higher.
The Microsoft SQL Server data provider offers direct access to Microsoft SQL Server data stores— and only SQL Server data stores (version 7.0 and greater). The System.Data.SqlClient namespace contains the types used by the SQL Server provider and offers the same basic functionality as the OLE DB provider. The key difference is that the SQL Server provider bypasses the OLE DB layer and gives numerous performance benefits. The Microsoft SQL Server data provider also allows you to gain access to the unique features of this particular DBMS.
The remaining Microsoft-supplied providers (System.Data.Odbc and System.Data.SqlClientCe) provide access to ODBC connections, and the SQL Server Mobile edition DBMS (commonly used by handheld devices, such as Windows Mobile). The ODBC types defined within the System.Data.Odbc namespace are typically only useful if you need to communicate with a given DBMS for which there is no custom .NET data provider. This is true because ODBC is a widespread model that provides access to a number of data stores.
Earlier versions of the .NET platform shipped with an assembly named System.Data.OracleClient.dll, which as the name suggests, offered a data provider to communicate with Oracle databases. As of .NET 4.0, however, this assembly has been marked as obsolete and will eventually be deprecated.
At first glance, this might cause you to fear that ADO.NET is slowly becoming focused squarely on Microsoft-centric data stores; however, this is not the case. Oracle provides its own custom .NET assembly, which follows the same overall design guidelines as the data providers provided by Microsoft. If you need to obtain this .NET assembly, you can visit the downloads section of Oracle’s web site at www.oracle.com/technology/tech/windows/odpnet/index.html.
In addition to the data providers that ship from Microsoft (as well as Oracle’s custom .NET library), numerous third-party data providers exist for various open source and commercial databases. While you will most likely be able to obtain an ADO.NET data provider directly from the database vendor, you should be aware of the following site: www.sqlsummit.com/DataProv.htm.
This web site is one of many sites that document each known ADO.NET data provider and provide links for more information and downloads. Here you will find numerous ADO.NET providers, including SQLite, IBM DB2, MySQL, PostgreSQL, TurboDB, Sybase, and many others.
Given the large number of ADO.NET data providers, the examples in this book will use the Microsoft SQL Server data provider (System.Data.SqlClient.dll). Recall that this provider allows you to communicate with Microsoft SQL Server version 7.0 and higher, including SQL Server Express Edition. If you intend to use ADO.NET to interact with another DBMS, you should have no problem doing so once you understand the material presented in the pages that follow.